import csv
import pymysql


conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='root',
                       db='test1',
                       charset='utf8')

def find_all_user_id():
    user_ids=[]
    sql="SELECT DISTINCT(train.`user_id`) FROM train"
    with  conn.cursor() as cur:
        try:
            cur.execute(sql)
            result=cur.fetchall()
            for id in result:
                user_ids.append(id[0])
        except Exception as e :
            print(e)
    return user_ids


def find_items_of_user(user_id):
    items=[]
    sql="SELECT DISTINCT(train.`item_id`),train.`cate_id` FROM train WHERE train.`user_id`=%s ORDER BY train.`action_time`"
    with  conn.cursor() as cur:
        try:
            cur.execute(sql,(user_id,))
            result=cur.fetchall()
            for id in result:
                items.append(id[1])
        except Exception as e :
            print(e)

    return items


def write():
    users=find_all_user_id()
    num=1
    with open(r'./train.txt','w',encoding='utf-8') as f:
        for user in users:
            print(num)
            num+=1
            items = find_items_of_user(user)
            items=' '.join(items)
            f.write(items)
            f.write('\n')


def n_every(n,l):
    '''
        将列表中的元素分为n个一组
    :return: [[][]] 列表的列表
    '''
    l_res = []

    if len(l)<n:
        l_tem=[]
        for i in range(n-len(l)):
            l_tem.append('-1')
        l_tem += l
        l_res.append(l_tem)
        return l_res

    i=0
    while i+n<=len(l):
        l_res.append(l[i:i+n])
        i+=1
    return l_res


def test_n_every():
    l1=[0, 2, 2, 5, 3, 5, 3, 0, 0, 3, 3, 6, 2, 6, 6, 3, 2, 6, 6, 0, 6, 6, 15]
    l2=[1,2,3,4]
    n=10
    res=n_every(n,l2)
    print(res)


def change_cate_id():
    sql='SELECT DISTINCT(train.`cate_id`) FROM train'
    global conn
    with conn.cursor() as cur:
        cur.execute(sql)
        result=cur.fetchall()
        dict_id={tu[0]:str(i) for i,tu in enumerate( result)}


    with open(r'./train.txt', 'r', encoding='utf-8') as f1:
        with open(r'./train2.txt', 'w', encoding='utf-8') as f2:
            lines=f1.readlines()
            num=0
            for line in lines:
                print(num)
                num+=1
                ids=line.split(' ')
                new_ids=[dict_id[id.strip()] for id in ids]
                for id in n_every(15,new_ids):
                    f2.write(' '.join(id))
                    f2.write('\n')

def test_train():
    with open('train2.txt','r',encoding='utf-8') as f:
        lines=f.readlines()
        with open('train3.txt','w',encoding='utf-8') as f1:
            for i in range(1200000):
                f1.write(lines[i])

        with open('test3.txt','w',encoding='utf-8') as f2:
            for i in range(100000):
                f2.write(lines[1200000+i])



def len_file(filenname):
    with open(filenname,'r',encoding='utf-8') as f:
        lines=f.readlines()
        return len(lines)

if __name__ == '__main__':
    # test_train()
    print(len_file('test3.txt'))
    print(len_file('train3.txt'))
    conn.close()

